﻿using QuanLyGiaoVuDAL.ADOWrapper;
using QuanLyGiaoVuDAL.DTO;
using QuanLyGiaoVuDAL.Mapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace QuanLyGiaoVuDAL.Repositories.DatabaseRepositories
{
    public class TaiLieuRepository : ITaiLieuRepository
    {
        public TaiLieuDTO GetTaiLieuById(Guid id)
        {
            var idParameter = DBParameterFactory.Create("MaTaiLieu", SqlDbType.UniqueIdentifier, id);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_TAILIEUSelect]", idParameter);
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToTaiLieuDTO();
        }

        public IEnumerable<TaiLieuDTO> GetAllTaiLieu(Guid? maThuMuc, bool? timThuMucCon, Guid? maLoaiTaiLieu, Guid? maNguoiTao, bool? chiaSe)
        {
            List<TaiLieuDTO> taiLieuList = new List<TaiLieuDTO>();
            var maThuMucParameter = DBParameterFactory.Create("MaThuMuc", SqlDbType.UniqueIdentifier, maThuMuc);
            var timThuMucConParameter = timThuMucCon.HasValue ? DBParameterFactory.Create("TimThuMucCon", SqlDbType.Bit, timThuMucCon) : DBParameterFactory.Create("TimThuMucCon", SqlDbType.Bit, DBNull.Value);
            var maLoaiTaiLieuParameter = DBParameterFactory.Create("MaLoaiTaiLieu", SqlDbType.UniqueIdentifier, maLoaiTaiLieu);
            var maNguoiTaoParameter = DBParameterFactory.Create("MaNguoiTao", SqlDbType.UniqueIdentifier, maNguoiTao);
            var chiaSeParameter = DBParameterFactory.Create("ChiaSe", SqlDbType.Bit, chiaSe);
            if (chiaSe == null)
                chiaSeParameter = DBParameterFactory.Create("ChiaSe", SqlDbType.Bit, DBNull.Value);
            
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_TAILIEUSelect]", maThuMucParameter, maLoaiTaiLieuParameter, maNguoiTaoParameter, chiaSeParameter, timThuMucConParameter);
            var taiLieuTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in taiLieuTable)
            {
                taiLieuList.Add(dataRow.ToTaiLieuDTO());
            }
            return taiLieuList.AsEnumerable();
        }


        public TaiLieuDTO InsertTaiLieu(TaiLieuDTO taiLieu)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_TAILIEUInsert]", GetParameterArray(taiLieu));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToTaiLieuDTO();
        }

        public TaiLieuDTO UpdateTaiLieu(TaiLieuDTO taiLieu)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_TAILIEUUpdate]", GetParameterArray(taiLieu));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToTaiLieuDTO();
        }

        public bool DeleteTaiLieu(Guid id)
        {
            SqlParameter idParameter = DBParameterFactory.Create("MaTaiLieu", SqlDbType.UniqueIdentifier, id);
            return StoreProcedureExecutor.ExecuteNonQueryStoredProcedure("[dbo].[usp_TAILIEUDelete]", idParameter);
        }

        #region Private methods

        private System.Data.SqlClient.SqlParameter[] GetParameterArray(TaiLieuDTO taiLieuDTO)
        {
            SqlParameter[] parameters = new SqlParameter[16];
            parameters[0] = DBParameterFactory.Create("MaTaiLieu", taiLieuDTO.MaTaiLieu);
            parameters[1] = DBParameterFactory.Create("TenTaiLieu", taiLieuDTO.TenTaiLieu);
            parameters[2] = DBParameterFactory.Create("TenTapTin", taiLieuDTO.TenTapTin);
            parameters[3] = DBParameterFactory.Create("PhanMoRong", taiLieuDTO.PhanMoRong);
            parameters[4] = DBParameterFactory.Create("MaTapTin", taiLieuDTO.MaTapTin);
            parameters[5] = DBParameterFactory.Create("MaLoaiTaiLieu", taiLieuDTO.MaLoaiTaiLieu);
            parameters[13] = DBParameterFactory.Create("MaNguoiTao", taiLieuDTO.MaNguoiTao);
            parameters[14] = DBParameterFactory.Create("NgayTao", taiLieuDTO.NgayTao);
            if (taiLieuDTO.NgayTao == null)
                parameters[14] = DBParameterFactory.Create("NgayTao", SqlDbType.DateTime, DBNull.Value);
            parameters[15] = DBParameterFactory.Create("NgaySua", taiLieuDTO.NgaySua);
            if (taiLieuDTO.NgaySua == null)
                parameters[15] = DBParameterFactory.Create("NgaySua", SqlDbType.DateTime, DBNull.Value);
            parameters[6] = DBParameterFactory.Create("MaThuMuc", taiLieuDTO.MaThuMuc);
            parameters[7] = DBParameterFactory.Create("Diem", SqlDbType.Int, taiLieuDTO.Diem);
            if (taiLieuDTO.Diem == null)
                parameters[7] = DBParameterFactory.Create("Diem", SqlDbType.Int, DBNull.Value);
            parameters[8] = DBParameterFactory.Create("DaKiemDuyet", taiLieuDTO.DaKiemDuyet);
            parameters[9] = DBParameterFactory.Create("DaXoa", taiLieuDTO.DaXoa);
            parameters[10] = DBParameterFactory.Create("GhiChu", taiLieuDTO.GhiChu);
            if (taiLieuDTO.GhiChu == null)
                parameters[10] = DBParameterFactory.Create("GhiChu", SqlDbType.NVarChar, DBNull.Value);
            parameters[11] = DBParameterFactory.Create("KichThuoc", SqlDbType.BigInt, taiLieuDTO.KichThuoc);
            if (taiLieuDTO.KichThuoc == null)
                parameters[11] = DBParameterFactory.Create("KichThuoc", SqlDbType.BigInt, DBNull.Value);
            parameters[12] = DBParameterFactory.Create("ChiaSe", SqlDbType.Bit, taiLieuDTO.ChiaSe);
            return parameters;
        }

        private System.Data.SqlClient.SqlParameter[] GetThuMucParameterArray(ThuMucDTO thuMucDTO)
        {
            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = DBParameterFactory.Create("MaThuMuc", thuMucDTO.MaThuMuc);
            parameters[1] = DBParameterFactory.Create("TenThuMuc", thuMucDTO.TenThuMuc);
            parameters[2] = DBParameterFactory.Create("MaThuMucCha", thuMucDTO.MaThuMucCha);
            
            if (thuMucDTO.MaThuMucCha == null)
                parameters[2] = DBParameterFactory.Create("MaThuMucCha", SqlDbType.UniqueIdentifier, DBNull.Value);
            return parameters;
        }
        #endregion


        public IEnumerable<ThuMucDTO> GetThuMucConByMaThuMucCha(Guid? id)
        {
            List<ThuMucDTO> thuMucList = new List<ThuMucDTO>();
            var maThuMucChaParameter = DBParameterFactory.Create("folderId", SqlDbType.UniqueIdentifier, id);
            if (id == null)
                maThuMucChaParameter = DBParameterFactory.Create("folderId", SqlDbType.UniqueIdentifier, DBNull.Value);

            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LoadAllChildFolderByParentFolderId]", maThuMucChaParameter);
            var taiLieuTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in taiLieuTable)
            {
                thuMucList.Add(dataRow.ToThuMucDTO());
            }
            return thuMucList.AsEnumerable();
        }


        public ThuMucDTO TaoThuMucMoi(ThuMucDTO thuMuc)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_THUMUCInsert]", GetThuMucParameterArray(thuMuc));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToThuMucDTO();
        }

        public ThuMucDTO SuaThuMuc(ThuMucDTO thuMuc)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_THUMUCUpdate]", GetThuMucParameterArray(thuMuc));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToThuMucDTO();
        }

        public bool XoaThuMuc(Guid id)
        {
            SqlParameter idParameter = DBParameterFactory.Create("MaThuMuc", SqlDbType.UniqueIdentifier, id);
            return StoreProcedureExecutor.ExecuteNonQueryStoredProcedure("[dbo].[usp_THUMUCDelete]", idParameter);
        }

        public IEnumerable<ThuMucDTO> GetThuMuc(Guid? maThuMuc)
        {
            List<ThuMucDTO> thuMucList = new List<ThuMucDTO>();
            var maThuMucParameter = DBParameterFactory.Create("MaThuMuc", SqlDbType.UniqueIdentifier, maThuMuc);
            if (maThuMuc == null)
                maThuMucParameter = DBParameterFactory.Create("MaThuMuc", SqlDbType.UniqueIdentifier, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_THUMUCSelect]", maThuMucParameter);
            var thuMucTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in thuMucTable)
            {
                thuMucList.Add(dataRow.ToThuMucDTO());
            }
            return thuMucList.AsEnumerable();
        }

        public String GetDuongDanThuMuc(Guid maThuMuc)
        {
            var idParameter = DBParameterFactory.Create("MaThuMuc", SqlDbType.UniqueIdentifier, maThuMuc);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LayDuongDanThuMuc]", idParameter);
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow["DuongDan"].ToString();
        }

        public IEnumerable<LoaiTaiLieuDTO> GetLoaiTaiLieu(Guid? maLoaiTaiLieu)
        {
            List<LoaiTaiLieuDTO> loaiTaiLieuList = new List<LoaiTaiLieuDTO>();
            var maLoaiTaiLieuParameter = DBParameterFactory.Create("MaLoaiTaiLieu", SqlDbType.UniqueIdentifier, maLoaiTaiLieu);
            if (maLoaiTaiLieu == null)
                maLoaiTaiLieuParameter = DBParameterFactory.Create("MaLoaiTaiLieu", SqlDbType.UniqueIdentifier, DBNull.Value);

            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LOAITAILIEUSelect]", maLoaiTaiLieuParameter);
            var loaiTaiLieuTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in loaiTaiLieuTable)
            {
                loaiTaiLieuList.Add(dataRow.ToLoaiTaiLieuDTO());
            }
            return loaiTaiLieuList.AsEnumerable();
        }


    }
}
